#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2017/9/22 0022 下午 11:47
# @Author  : toby
# @Site    : 
# @File    : mydb.py
# @Software: PyCharm

import pymysql


# from excel_com.comm import Comm
import time
from configparser import ConfigParser
from pymysql.constants import CLIENT


class MySqlDb:
    # 连接数据库
    def __init__(self):
        # 读取配置文件 ,链接数据库
        # conf = ConfigParser()
        # conf.read(config_path, encoding='utf-8')
        #
        # # db数据库配置地址
        # HOST = conf.get(tag, 'HOST')
        # PORT = conf.get(tag, 'PORT')
        # USER = conf.get(tag, 'USER')
        # PASSWD = conf.get(tag, 'PASSWD')
        # DBNAME = conf.get(tag, 'DBNAME')
        try:
            # 打开数据库连接
            self.conn = pymysql.connect(host="gz-cdb-hn9omw23.sql.tencentcdb.com",
                                        port= 56781,
                                        user="autochargeadmin",
                                        passwd="Xlr_ganha2015",
                                        db="autocharge",
                                        charset='utf8',
                                        client_flag=CLIENT.MULTI_STATEMENTS ) # 添加client_flag参数)
            # 获取游标
            self.cursor = self.conn.cursor()
            print('Get curse success!')

        except Exception as e :
            print(" 数据库连接失败 :{}".format(e))

    # sql语句执行,返回字典类型
    def select_sql(self,sql_string):
        '''
        run the query_sql
        :param sql_string:
        :return:
        '''
        self._query = sql_string
        print('查询语句:{}'.format(self._query))
        try:
            self.cursor.execute(query=self._query)
            coloumns = [row[0] for row in self.cursor.description]
            result = [[str(item) for item in row] for row in self.cursor.fetchall()]
            print('查询结果返回 ：')
            return [dict(zip(coloumns, row)) for row in result]
        except:
            self.conn.rollback()
            print('执行查询的失败 ')
            return None


    # 返回tuple类型  # 可以用来执行update等语句
    def sql_run(self,sql_string):
        self.query = sql_string
        print("执行SQL语句:{}".format(self.query))

        try:
            # 执行 ,提交 ,返回执行结果
            self.cursor.execute(query=self.query)
            self.conn.commit()  #需要使用commint()才能操作提交成功
        except Exception as e :
            self.conn.rollback()
            print('执行sql失败:',e)
            return None

    # 断开数据库
    def close(self):
        self.cursor.close()
        self.conn.close()
        print('mysql closed')



if __name__ == '__main__':
    path = "./cfg.ini"
    tag = "mysql"
    mydb = MySqlDb()
    sql =  'SELECT * FROM t_charge_user WHERE mobile ="13189593801";  '
    res = mydb.select_sql(sql)
    print(res[0]['userId'])

    sql = 'SELECT * FROM t_charge_deal WHERE userId ='+res[0]['userId']  +'  ORDER BY dealId DESC limit 1;'
    res = mydb.select_sql(sql)
    print(res[0]['dealId'])

    mydb.close()





